﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Report_Unity.Basic;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace Report_Unity.Unitys
{
    public class ExcelUtils
    {
        public static byte[] GetExcelTemlate<T>()
        {
            Type type = typeof(T);
            PropertyInfo[] props = type.GetProperties();
            List<string> list = new List<string>();
            foreach (PropertyInfo p in props)
            {
                if (p.IsDefined(typeof(RemarkAttribute)))
                {
                    RemarkAttribute remark = p.GetCustomAttribute(typeof(RemarkAttribute)) as RemarkAttribute;
                    if (remark != null)
                        list.Add(remark.GetRemark());
                }
            }

            // 然后写入到excel中
            IWorkbook wb = new XSSFWorkbook();
            ISheet sheet = wb.CreateSheet();
            IRow row = sheet.CreateRow(0);

            for (int i = 0; i < list.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                SetCellValue(cell, list[i]);
            }

            NPOIMemoryStream ms = null;
            byte[] bys = new byte[0];
            try
            {
                ms = new NPOIMemoryStream();
                wb.Write(ms);
                bys = ms.ToArray();
            }
            finally
            {
                if (ms != null)
                    ms.Close();
            }

            return bys;
        }       

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T">返回对象</typeparam>
        /// <typeparam name="V">参照类</typeparam>
        /// <param name="extName"></param>
        /// <param name="stream"></param>
        /// <param name="isFirstColumnName"></param>
        /// <returns></returns>
        public static List<T> ReadContent<T>(HttpPostedFile postedFile) where T : class
        {
            if (postedFile == null)
                throw new ArgumentNullException("无excel文件");
            Stream stream = postedFile.InputStream;
            string extName = Path.GetExtension(postedFile.FileName);
            IWorkbook workbook;
            if (extName.Equals(".xls", StringComparison.OrdinalIgnoreCase))
            {
                workbook = new HSSFWorkbook(stream);
            }
            else if (extName.Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
            {
                workbook = new XSSFWorkbook(stream);
            }
            else
            {
                throw new FormatException("文件格式不正确，只支持xls和xlsx");
            }

            List<T> list = null;
            try
            {
                list = new List<T>();
                ISheet sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet  
                if (sheet != null)
                {
                    // 获取第一行
                    int rowNum = sheet.LastRowNum + 1;
                    IRow firstRow = sheet.GetRow(0);
                    int cellNum = firstRow.LastCellNum; // 列数

                    for (int i = 0; i < rowNum; i++)
                    {

                        // 跳过第一行
                        if (i == 0)
                            continue;

                        // 创建数据
                        T t = Activator.CreateInstance<T>();
                        IRow row = sheet.GetRow(i);

                        for (int j = 0; j < cellNum; j++)
                        {
                            ICell firstRowCell = firstRow.GetCell(j); // 第一行单元格
                            ICell rowCell = row.GetCell(j);
                            rowCell.SetCellType(CellType.String);
                            PropertyInfo[] props = t.GetType().GetProperties();
                            foreach (PropertyInfo p in props)
                            {
                                if (p.IsDefined(typeof(RemarkAttribute)))
                                {
                                    RemarkAttribute remarkAttribute = p.GetCustomAttribute(typeof(RemarkAttribute)) as RemarkAttribute;
                                    string remark = remarkAttribute.GetRemark();
                                    // 第一行的文字和标注对比
                                    string cellValue = rowCell.StringCellValue;
                                    if (firstRowCell.StringCellValue == remark)
                                    {
                                        p.SetValue(t, cellValue);
                                    }
                                }
                            }
                        }

                        list.Add(t);
                    }
                }
            }
            finally
            {
                if (stream != null)
                {
                    stream.Close();
                    workbook.Close();
                }
            }

            return list;
        }

        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="K">导出类的类型</typeparam>
        /// <typeparam name="T">比较字段类的类型</typeparam>
        /// <param name="code"></param>
        /// <param name="cName"></param>
        /// <param name="spit"></param>
        /// <param name="listData"></param>
        /// <param name="rows"></param>
        /// <param name="act">web或者流写Excel</param>
        public static NPOIMemoryStream CreateExcel<K, T>(int version, string cName, string spit, List<K> listData, int rows, string sheetName, out string fileName)
        {
            string fileExtends = string.Empty;
            int maxRowsCount = 60000;
            //创建工作薄  
            IWorkbook wb;
            if (version == 2003)
            {
                maxRowsCount = 60000;
                fileExtends = ".xls";
                wb = new HSSFWorkbook();
            }
            else
            {
                maxRowsCount = 400000;
                fileExtends = ".xlsx";
                wb = new XSSFWorkbook();
            }

            int pageCount = 0;
            if (rows % maxRowsCount == 0)
                pageCount = rows / maxRowsCount;
            else
                pageCount = rows / maxRowsCount + 1;

            int remainder = rows % maxRowsCount; // 余数
            List<K> listSkip = new List<K>();
            // 分sheet
            for (int y = 0; y < pageCount; y++)
            {
                if (pageCount == 0)
                {
                    // rows等于0
                    WriteExcel<K, T>(wb, spit, y, cName, 0, listData, sheetName);
                }
                else if (pageCount == 1)
                {
                    // 全部打印
                    WriteExcel<K, T>(wb, spit, y, cName, rows, listData, sheetName);
                }
                else
                {
                    // 最后一页
                    if (y + 1 == pageCount)
                    {
                        // 余数等于0
                        if (remainder == 0)
                        {
                            listSkip = listData.Skip((pageCount - 1) * maxRowsCount).Take(remainder + 1).ToList();
                            WriteExcel<K, T>(wb, spit, y, cName, remainder + 1, listSkip, sheetName); // 这里用最大数
                        }
                        else
                        {
                            listSkip = listData.Skip((pageCount - 1) * maxRowsCount).Take(remainder).ToList();
                            WriteExcel<K, T>(wb, spit, y, cName, remainder, listSkip, sheetName); // 这里用最大数
                        }

                    }
                    else
                    {
                        // 跳过n条数据
                        listSkip = listData.Skip(y * maxRowsCount).Take(maxRowsCount).ToList();
                        WriteExcel<K, T>(wb, spit, y, cName, maxRowsCount, listSkip, sheetName); // 这里用余数
                    }
                }
            }

            fileName = GetFileName(sheetName, fileExtends);
            NPOIMemoryStream ms = new NPOIMemoryStream();
            wb.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);

            return ms;
        }

        private static string GetFileName(string sheetName, string fileExtends)
        {
            DateTime date = DateTime.Now;
            string year = date.Year.ToString();
            string month = date.Month < 10 ? "0" + date.Month.ToString() : date.Month.ToString();
            string day = date.Day < 10 ? "0" + date.Day.ToString() : date.Day.ToString();
            string hour = date.Hour < 10 ? "0" + date.Hour.ToString() : date.Hour.ToString();
            string minute = date.Minute < 10 ? "0" + date.Minute.ToString() : date.Minute.ToString();
            string seconds = date.Second < 10 ? "0" + date.Second.ToString() : date.Second.ToString();
            string fileTime = year + month + day + hour + minute + seconds;

            return sheetName + "_" + fileTime + fileExtends; // 订单报表_20200515103626.xls
        }


        private static void WriteExcel<K, T>(IWorkbook wb, string spit, int y, string cName, int rows, List<K> listData, string sheetName)
        {
            ISheet sheet = wb.CreateSheet($"{sheetName}_{y + 1}");
            char chsSpit = spit.ToCharArray()[0];
            string[] columns = cName.Split(chsSpit);
            int columnCount = columns.Length;

            IRow row;
            ICell cell;

            // 所有需要显示的行
            IDictionary<string, string> dic = GetColumn<T>();
            //List<string> listNames = new List<string>(); // 需要展示列名
            Dictionary<string, string> dicColumn = new Dictionary<string, string>(); // 需要导出excel的列名
            foreach (string pName in columns)
            {
                dicColumn.Add(pName.ToLower(), dic[pName.ToLower()]);
            }

            List<string> listCloumnNames = dicColumn.Values.ToList();

            for (int i = 0; i < rows + 1; i++)
            {
                // 第一行
                row = sheet.CreateRow(i);
                if (i == 0)
                {
                    for (int j = 0; j < columnCount; j++)
                    {
                        string firstRowName = listCloumnNames[j];
                        cell = row.CreateCell(j); // 创建单元格
                        SetCellValue(cell, firstRowName);
                    }
                }
                else
                {
                    // 第二行开始插入数据
                    K kObj = listData[i - 1];
                    Type type = kObj.GetType();
                    PropertyInfo[] props = type.GetProperties();

                    int k = 0;

                    foreach (var kv in dicColumn)
                    {
                        foreach (PropertyInfo p in props)
                        {
                            if (kv.Key.ToLower().Equals(p.Name.ToLower()))
                            {
                                cell = row.CreateCell(k);
                                object val = p.GetValue(kObj);
                                SetCellValue(cell, val);
                                k++;
                            }
                        }
                    }
                }
            }
        }

        public static IDictionary<string, string> GetColumn<T>()
        {
            return GetColumn(typeof(T));
        }

        public static IDictionary<string, string> GetColumn(Type type)
        {
            IDictionary<string, string> dic = new Dictionary<string, string>();
            PropertyInfo[] props = type.GetProperties();
            foreach (PropertyInfo p in props)
            {
                string key = p.Name;
                string val = string.Empty;

                if (p.IsDefined(typeof(RemarkAttribute)))
                {
                    RemarkAttribute attr = p.GetCustomAttribute(typeof(RemarkAttribute)) as RemarkAttribute;
                    val = attr.GetRemark();
                    dic.Add(key.ToLower(), val);
                }
            }

            return dic;
        }

        public static object GetCellValue(ICell cell)
        {
            object value = null;
            try
            {
                if (cell.CellType != CellType.Blank)
                {
                    switch (cell.CellType)
                    {
                        case CellType.Numeric:
                            // Date comes here
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                value = cell.DateCellValue;
                            }
                            else
                            {
                                // Numeric type
                                value = cell.NumericCellValue;
                            }
                            break;
                        case CellType.Boolean:
                            // Boolean type
                            value = cell.BooleanCellValue;
                            break;
                        case CellType.Formula:
                            value = cell.CellFormula;
                            break;
                        default:
                            // String type
                            value = cell.StringCellValue;
                            break;
                    }
                }
            }
            catch (Exception)
            {
                value = "";
            }

            return value;
        }

        //根据数据类型设置不同类型的cell
        public static void SetCellValue(ICell cell, object obj)
        {
            if (obj != null)
            {
                if (obj.GetType() == typeof(int))
                {
                    cell.SetCellValue((int)obj);
                }
                else if (obj.GetType() == typeof(double))
                {
                    cell.SetCellValue((double)obj);
                }
                else if (obj.GetType() == typeof(IRichTextString))
                {
                    cell.SetCellValue((IRichTextString)obj);
                }
                else if (obj.GetType() == typeof(string))
                {
                    cell.SetCellValue(obj.ToString());
                }
                else if (obj.GetType() == typeof(DateTime))
                {
                    cell.SetCellValue(obj.ToString());
                    // cell.SetCellValue((DateTime)obj);
                }
                else if (obj.GetType() == typeof(Nullable<System.DateTime>))
                {
                    if (obj != null)
                        cell.SetCellValue((DateTime)obj);
                }
                else if (obj.GetType() == typeof(bool))
                {
                    cell.SetCellValue((bool)obj);
                }
                else
                {
                    cell.SetCellValue(obj.ToString());
                }
            }
            else
            {
                cell.SetCellValue("");
            }
        }
    }

    public class NPOIMemoryStream : MemoryStream
    {
        /// <summary>
        /// 获取流是否关闭
        /// </summary>
        public bool IsColse
        {
            get;
            private set;
        }

        public NPOIMemoryStream(bool colse = false)
        {
            IsColse = colse;
        }

        public override void Close()
        {
            if (IsColse)
            {
                base.Close();
            }

        }
    }
}
